package com.edu.utils;
/**
 * 数据库访问帮助类
 *
 * @author ZCN
 */

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class JdbcHelper {

    private static Connection conn = null;
    private static PreparedStatement preparedStatement = null;

    /**
     * 用于查询，返回结果集
     *
     * @param sql sql语句
     * @return 结果集
     * @throws SQLException
     */
    @SuppressWarnings("rawtypes")
    public static List query(String sql,Class z) throws SQLException {

        ResultSet rs = null;
        try {
            getPreparedStatement(sql);
            rs = preparedStatement.executeQuery();

            return ResultToListMap(rs,z);
        } catch (SQLException e) {
            throw new SQLException(e);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(conn != null){
                conn.close();
            }
        }
        return null;
    }

    /**
     * 用于带参数的查询，返回结果集
     *
     * @param sql       sql语句
     * @param paramters 参数集合
     * @return 结果集
     * @throws SQLException
     */
    @SuppressWarnings("rawtypes")
    public static List query(String sql,Class z, Object... paramters)
            throws SQLException {

        ResultSet rs = null;
        try {
            getPreparedStatement(sql);

            for (int i = 0; i < paramters.length; i++) {
                preparedStatement.setObject(i + 1, paramters[i]);
            }
            rs = preparedStatement.executeQuery();
            return ResultToListMap(rs,z);
        } catch (SQLException e) {
            throw new SQLException(e);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(conn != null){
                conn.close();
            }
        }
        return null;
    }

    /**
     * 返回单个结果的值，如count\min\max等等
     *
     * @param sql sql语句
     * @return 结果集
     * @throws SQLException
     */
    public static Object getSingle(String sql) throws SQLException {
        Object result = null;
        ResultSet rs = null;
        try {
            getPreparedStatement(sql);
            rs = preparedStatement.executeQuery();
            if (rs.next()) {
                result = rs.getObject(1);
            }
            return result;
        } catch (SQLException e) {
            throw new SQLException(e);
        } finally {
            if(conn != null){
                conn.close();
            }
        }

    }

    /**
     * 返回单个结果值，如count\min\max等
     *
     * @param sql       sql语句
     * @param paramters 参数列表
     * @return 结果
     * @throws SQLException
     */
    public static Object getSingle(String sql, Object... paramters)
            throws SQLException {
        Object result = null;
        ResultSet rs = null;
        try {
            getPreparedStatement(sql);

            for (int i = 0; i < paramters.length; i++) {
                preparedStatement.setObject(i + 1, paramters[i]);
            }
            rs = preparedStatement.executeQuery();
            if (rs.next()) {
                result = rs.getObject(1);
            }
            return result;
        } catch (SQLException e) {
            throw new SQLException(e);
        } finally {
            if(conn != null){
                conn.close();
            }
        }
    }

    /**
     * 用于增删改
     *
     * @param sql sql语句
     * @return 影响行数
     * @throws SQLException
     */
    public static int update(String sql) throws SQLException {

        try {
            getPreparedStatement(sql);

            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new SQLException(e);
        } finally {
            if(conn != null){
                conn.close();
            }
        }
    }

    /**
     * 用于增删改（带参数）
     *
     * @param sql       sql语句
     * @param paramters sql语句
     * @return 影响行数
     * @throws SQLException
     */
    public static int update(String sql, Object... paramters)
            throws SQLException {
        try {
            getPreparedStatement(sql);

            for (int i = 0; i < paramters.length; i++) {
                preparedStatement.setObject(i + 1, paramters[i]);
            }
            return preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new SQLException(e);
        } finally {
            if(conn != null){
                conn.close();
            }
        }
    }

    /**
     * 插入值后返回主键值
     *
     * @param sql 插入sql语句
     * @return 返回结果
     * @throws Exception
     */
    public static Object insertWithReturnPrimeKey(String sql)
            throws SQLException {
        ResultSet rs = null;
        Object result = null;
        try {
            conn = DBCPUtil.getConnection();
            preparedStatement = conn.prepareStatement(sql,
                    PreparedStatement.RETURN_GENERATED_KEYS);
            preparedStatement.execute();
            rs = preparedStatement.getGeneratedKeys();
            if (rs.next()) {
                result = rs.getObject(1);
            }
            return result;
        } catch (SQLException e) {
            throw new SQLException(e);
        }
    }

    /**
     * 插入值后返回主键值
     *
     * @param sql       插入sql语句
     * @param paramters 参数列表
     * @return 返回结果
     * @throws SQLException
     */
    public static Object insertWithReturnPrimeKey(String sql,
                                                  Object... paramters) throws SQLException {
        ResultSet rs = null;
        Object result = null;
        try {
            conn = DBCPUtil.getConnection();
            preparedStatement = conn.prepareStatement(sql,
                    PreparedStatement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < paramters.length; i++) {
                preparedStatement.setObject(i + 1, paramters[i]);
            }
            preparedStatement.execute();
            rs = preparedStatement.getGeneratedKeys();
            if (rs.next()) {
                result = rs.getObject(1);
            }
            return result;
        } catch (SQLException e) {
            throw new SQLException(e);
        }

    }

    // 将结果集转换成list
    private static List ResultToListMap(ResultSet rs,Class clazz) throws Exception {

        List list = new ArrayList();
        while (rs.next()) {
            ResultSetMetaData md = rs.getMetaData();
            Object o = clazz.getConstructor().newInstance();
            for (int i = 1; i < md.getColumnCount() + 1; i++) {
                String label = md.getColumnLabel(i);
                Field field = clazz.getDeclaredField(label);
                String str=field.getName();
                String s = str.substring(0, 1).toUpperCase() + str.substring(1);
                Method method = clazz.getMethod("set" + s, field.getType());
                method.invoke(o,rs.getObject(i));
            }
            list.add(o);
        }
        return list;
    }


    /**
     * 获取PreparedStatement
     *
     * @param sql
     * @throws SQLException
     */
    private static void getPreparedStatement(String sql) throws SQLException {
        conn = DBCPUtil.getConnection();
        preparedStatement = conn.prepareStatement(sql);
    }

}